prompt [住院]费用拆分业务过程 SP_ZY_FYJS00_FYCFJS
create or replace procedure SP_ZY_FYJS00_FYCFJS
(
PBRID00 in number ,--[1][入参]:BRID00
PZYID00 in number ,--[2][入参]:ZYID00
PZYH000 in varchar2 ,--[3][入参]:住院号
PJZDH00 in number ,--[4][入参]:结账单号
PCZY000 in number ,--[5][入参]:操作员编码
PCZYXM0 in varchar2 ,--[6][入参]:操作员姓名
PCZYKS0 in number ,--[7][入参]:操作员科室
PCZLX00 in varchar2 ,--[8][入参]:操作类型 1:出院结算 2:重新结算
PFYCFLX in varchar2 ,--[9][入参]:费用拆分类型 N:否 Y:婴儿拆分 M:医保项目拆分 8:医保跨年拆分
PTYPE00 in varchar2 ,--[10][入参]:类型 中途结算、医保病人年度结算
PQTCS00 in varchar2 ,--[11][入参]:其他参数(xml)
PHJJE00 in number ,--[12][入参]:合计金额
PCOMMIT in varchar2 ,--[13][入参]:是否提交事务 Y:是 N:否
PZXZT00 out varchar2 ,--[14][出参]:执行状态 0:失败 1:成功
PZFHJJE out number ,--[15][出参]:自费合计金额或上年度合计金额
PYBHJJE out number ,--[16][出参]:医保合计金额或下年度合计金额
PFKLSH0 out varchar2 ,--[17][出参]:分开结算流水号
PNDJSXX out varchar2 ,--[18][出参]年度结算返回信息=SF_ZY_SFQYYBKNDJS返回值
POUTSTR out varchar2 ,--[19][出参]备用出参xml格式输出
PERCODE out varchar2 ,--[20][出参]:错误编码
PERRMSG out varchar2 --[21][出参]:错误信息
)
as
-- MODIFICATION HISTORY
-- Person Date Comments
-- zhangyc 2020.12.28 created by ZYSF-20201229-004
-- zhangyc 2022.01.04 增加IC_YBBRLB.YBSDLB=2(不分开结算) 医保属地类别；0本地、1省内、2省外（未定义的都默认为本地0） by ZYSF-20220105-005
-- zhangyc 2022.01.05 增加drg相关信息判断
LS_COUNT0 number(12);
LS_CZRQ00 char(8);
LS_CZSJ00 char(8);
LS_YBBRLB BM_BRXXB0.YBBRLB%type; --医保病人类别
LS_YBLB00 BM_BRXXB0.YBLB00%type; --医保类别
LS_YBZXLB IC_YBBRLB.YBZXLB%type; --医保中心类别编码
LS_FBBH00 BM_BRXXB0.FBBH00%type; --病人费别
LS_CXLSH0 PAY_YJJ_MXCX.CXLSH0%type;
LS_DDLSH0 PAY_YJJ_MXCX.DDLSH0%type;
LS_JKFH00 varchar2(10); --是否启用 Y:启用 N:不启用
LS_YBNDCS varchar2(100); --年度结算参数
LS_NDCS00 varchar2(20); --年度结算启用参数
LS_NDCS01 varchar2(100); --年度结算启用分中心
LS_RYRQ00 ZY_BRXXB0.RYRQ00%type; --入院日期
LS_DBZBM0 ZY_BRXXB0.DBZBM0%type; --单病种编码
LS_RYLB00 ZY_BRXXB0.RYLB00%type; --入院类别
LS_SFJZFP ZY_BRXXB0.SFJZFP%type; --是否精准扶贫 Y:是 1：是
LS_BZBMLB ZY_BRXXB0.BZBMLB%type; --病种编码类别 0:单病种编码 1:精准扶贫编码 2:出院诊断
LS_YBMC00 IC_YBBRLB.YBMC00%type; --医保中心名称
LS_TSBZFL BM_TSBZB0.TSBZFL%type;--病种分类 0:特殊病种 1:生育病种 2:单病种
LS_DQRQ00 varchar2(8);--当前日期
LS_SNDRQ0 varchar2(8);--上年度日期
LS_SNDFY0 number(12,2);--上年度费用
LS_BNDRQ0 varchar2(8);--本年度日期
LS_BNDFY0 number(12,2);--本年度费用
LS_BQBM00 varchar2(20);--病情编码
LS_ID0000_SND BM_ZYCF00_TEMP00.ID0000%type; --上年度
LS_ID0000_BND BM_ZYCF00_TEMP00.ID0000%type; --本年度
LS_CZBZ00 varchar2(2);--操作标志 0:正常结算 1:中途结算 2:年度结算 3:逃跑结算 01:跨年上年度 02:跨年本年度
LS_SFLB00 varchar2(1);--收费类别0:项目 1:YF_ZYCFMX 2:YF_YZYPSQ 3:药品 S:跨年上年度 B:跨年本年度
LS_YBSDLB IC_YBBRLB.YBSDLB%type; --医保属地类别；0本地、1省内、2省外（未定义的都默认为本地0）
ZY_DRGLSH ZY_BRXXB0.DRGLSH%type;--drglsh:DRG分组流水号
ZY_DRG_CODE ZY_BRXXB0.DRG_CODE%type;--drg_code:DRG组编码
ZY_DRG_NAME ZY_BRXXB0.DRG_NAME%type;--drg_name:DRG组名称
ZY_BATCHNUM ZY_BRXXB0.BATCHNUM%type;--batchnum:批次号
ZY_DRGZFE ZY_BRXXB0.DRGZFE%type;--drgzfe:DRG付费标准
ZY_DRG_STATUS ZY_BRXXB0.DRG_STATUS%type; --DRG同步状态:(0:撤销病案，1:病案上传，2:撤销明细，3:上传明细，4:分组查询，5:项目结算)
ZY_JSBLLX ZY_BRXXB0.JSBLLX%type;--DRG结算病例类型(BM_YBSXZD.JKBM00=90 SXBM00=jsbllx)(1:按DRG结算 2:按项目结算 3:按床日结算 4:日间手术)
ZY_IN_DRG ZY_BRXXB0.IN_DRG%type;--DRG入组标识编码(BM_YBSXZD.JKBM00=90 SXBM00=in_drg)
begin
PZXZT00:='0';--输出:执行状态 0:失败 1:成功
LS_CZRQ00:=to_char(sysdate,'YYYYMMDD');
LS_CZSJ00:=to_char(sysdate,'HH24:MI:SS');
PZFHJJE:=0;--输出:自费合计金额
PYBHJJE:=0;--输出:医保合计金额
PERCODE:='';--输出:错误编码
PERRMSG:='';--输出:错误信息
PFKLSH0:='FYCF'||to_char(sysdate,'YYYYMMDDHH24MISS')||PZYID00;
LS_CXLSH0:=PFKLSH0;
LS_DDLSH0:=PFKLSH0;
LS_DQRQ00:=to_char(sysdate,'YYYYMMDD');
LS_SNDFY0:=0;--上年度费用
LS_BNDFY0:=0;--本年度费用
LS_SNDRQ0:=to_number(substrb(LS_DQRQ00,1,4))-1||'1231';--上年度日期
LS_BNDRQ0:=LS_DQRQ00;--本年度日期
LS_JKFH00:='N';
PNDJSXX:='';--[出参]年度结算返回信息=SF_ZY_SFQYYBKNDJS返回值
begin
select A.YBBRLB,A.FBBH00,A.YBLB00,B.YBZXLB,C.RYRQ00,C.DBZBM0,C.RYLB00,C.SFJZFP,C.BZBMLB,nvl(B.YBSDLB,0) YBSDLB,
C.DRGLSH,C.DRG_CODE,C.DRG_NAME,C.BATCHNUM,C.DRGZFE,C.DRG_STATUS,C.JSBLLX,C.IN_DRG
into LS_YBBRLB,LS_FBBH00,LS_YBLB00,LS_YBZXLB,LS_RYRQ00,LS_DBZBM0,LS_RYLB00,LS_SFJZFP,LS_BZBMLB,LS_YBSDLB,
ZY_DRGLSH,ZY_DRG_CODE,ZY_DRG_NAME,ZY_BATCHNUM,ZY_DRGZFE,ZY_DRG_STATUS,ZY_JSBLLX,ZY_IN_DRG
from BM_BRXXB0 A,IC_YBBRLB B,ZY_BRXXB0 C
where A.FBBH00=B.FBBH00 and a.YBLB00=b.YBLB00 and A.BRID00=C.BRID00
and A.BRID00 = PBRID00 and C.ZYID00=PZYID00;
exception
when others then
LS_YBBRLB:='0';
LS_YBLB00:='0';
LS_YBZXLB:='0';
end;
if PFYCFLX='8' and nvl(PTYPE00,'出院结算') not in ('医保病人年度结算','中途结算','欠费出院登记') then --参考过程SF_ZY_SFQYYBKNDJS
--步骤1：获取参数值
--ZY_YBBRCYJSSFQYKNJSMS:住院医保病人出院结算是否启用跨年结算模式,0:不启用,2:医保都启用,1:选择启用,加分号后面带启用的医保中心,多个中心用逗号隔开,如（1;@,H,2）默认0
begin
select substrb(trim(VALUE0),1,100) into LS_YBNDCS from XT_XTCS00 where NAME00 in ('ZY_YBBRCYJSSFQYKNJSMS');
exception
when others then
LS_YBNDCS:='0';
end;
LS_NDCS00:=trim(substrb(LS_YBNDCS,1,1));--0:不启用,2:医保都启用,1:选择启用
LS_NDCS01:=trim(substrb(LS_YBNDCS,3,100));--多个中心
if LS_NDCS00 in ('2') then --2:医保都启用
LS_JKFH00:='Y'; --Y:拆分 N:不拆分
elsif LS_NDCS00 in ('1') then --1:选择启用
LS_JKFH00:='Y'; --Y:拆分 N:不拆分
else
LS_JKFH00:='N'; --Y:拆分 N:不拆分
end if;
--判断医保中心
if LS_NDCS00 in ('1') then --1:选择启用
if instrb(','||trim(LS_NDCS01)||',',','||trim(LS_YBZXLB)||',')>0 then
LS_JKFH00:='Y'; --Y:拆分 N:不拆分
else
LS_JKFH00:='N'; --Y:拆分 N:不拆分
end if;
end if;
--判断入院日期和当前日期 是同一年度,不处理--begin--
if substrb(LS_RYRQ00,1,4)=substrb(LS_DQRQ00,1,4) then
LS_JKFH00:='N'; --Y:拆分 N:不拆分
end if;
--判断入院日期和当前日期 是同一年度,不处理--End--
--2019.12.24 zhangyc 单病种 、生育类别（生育,生育实时刷卡住院） 不启用--begin--
--2022.01.04 IC_YBBRLB.YBSDLB=2(不分开结算) 医保属地类别；0本地、1省内、2省外（未定义的都默认为本地0）
if (LS_DBZBM0 is not null) or (LS_RYLB00 in ('生育','生育实时刷卡住院')) or (LS_YBSDLB in (2))then
LS_JKFH00:='N'; --Y:拆分 N:不拆分
end if;
--2019.12.24 zhangyc 单病种 、生育类别（生育,生育实时刷卡住院）不启用--End--
--2020.01.09
--福州市医保，扶贫病种编码，要求拆分(肺科医院)--begin--
if LS_BZBMLB='1' and LS_YBMC00 in ('福州市医保中心') then
LS_JKFH00:='Y'; --Y:拆分 N:不拆分
end if;
--福州市医保，扶贫病种编码，要求拆分(肺科医院)--end--
--病种分类 0:特殊病种 1:生育病种 2:单病种--begin--
LS_BQBM00:=substrb(SF_SF_GETXMLNOTE('BQBM00',PQTCS00),1,20);
if trim(LS_BQBM00) is not null then
begin
select TSBZFL into LS_TSBZFL from BM_TSBZB0 where trim(BH0000)=trim(LS_BQBM00) and YBZXLB=LS_YBZXLB;
exception
when others then
LS_TSBZFL:='';
end;
--1:生育病种 2:单病种 不拆分--begin--
if LS_TSBZFL in ('1','2') then
LS_JKFH00:='N'; --Y:拆分 N:不拆分
end if;
--1:生育病种 2:单病种 不拆分--end--
end if;
--病种分类 0:特殊病种 1:生育病种 2:单病种--end--
--2023.01.05 jsbllx：1:按DRG结算 2:按项目结算 3:按床日结算 4:日间手术
if ZY_JSBLLX is not null and ZY_JSBLLX not in ('2') then
LS_JKFH00:='N'; --Y:拆分 N:不拆分
end if;
--步骤3：判断上年度是否有没有费用
if LS_JKFH00='Y' then
--上年度费用
select SQ_ZY_TEMP00_ID0000.nextval into LS_ID0000_SND from dual;
LS_CZBZ00:='01';--操作标志 0:正常结算 1:中途结算 2:年度结算 3:逃跑结算 01:跨年上年度 02:跨年本年度
LS_SFLB00:='S';--收费类别0:项目 1:YF_ZYCFMX 2:YF_YZYPSQ 3:药品 S:跨年上年度 B:跨年本年度
insert into ZY_JSBCMX(ZYID00,ID0000,MXID00,SFLB00,XMBH00,JZDH00,DJH000,XMDJ00,
XMSL00,HJJE00,CZRQ00,CZSJ00,CZY000,CZBZ00,FZDDH0,BZ0000)
select PZYID00,LS_ID0000_SND,0,LS_SFLB00,0,PJZDH00,A.DJH000,a.HJJE00,
1,a.HJJE00,LS_CZRQ00,LS_CZSJ00,PCZY000,LS_CZBZ00,substrb(LS_DDLSH0,1,30),'FYCF_SND'
from ZY_BRFY00 a where 1=1
and a.ZYID00=PZYID00
and a.BRID00=PBRID00
and CZRQ00<=LS_SNDRQ0
and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
;
--冲销的单据号
insert into ZY_JSBCMX(ZYID00,ID0000,MXID00,SFLB00,XMBH00,JZDH00,DJH000,XMDJ00,
XMSL00,HJJE00,CZRQ00,CZSJ00,CZY000,CZBZ00,FZDDH0,BZ0000)
select PZYID00,LS_ID0000_SND,0,LS_SFLB00,0,PJZDH00,A.DJH000,a.HJJE00,
1,a.HJJE00,LS_CZRQ00,LS_CZSJ00,PCZY000,LS_CZBZ00,substrb(LS_DDLSH0,1,30),'FYCF_SND'
from ZY_BRFY00 a where 1=1
and a.ZYID00=PZYID00
and a.BRID00=PBRID00
and a.CXDJH0<>0
and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
and a.CXDJH0 in (select bb.DJH000 from ZY_JSBCMX bb where bb.ZYID00=PZYID00 and bb.ID0000=LS_ID0000_SND and bb.CZBZ00='01' )
and a.DJH000 not in (select bb.DJH000 from ZY_JSBCMX bb where bb.ZYID00=PZYID00 and bb.ID0000=LS_ID0000_SND and bb.CZBZ00='01')
;
begin
select sum(HJJE00) into LS_SNDFY0 from ZY_JSBCMX
where 1=1
and ZYID00=PZYID00
and ID0000=LS_ID0000_SND
and CZBZ00='01'
;
exception
when others then
LS_SNDFY0:=0;
end;
--本年度费用
select SQ_ZY_TEMP00_ID0000.nextval into LS_ID0000_BND from dual;
LS_CZBZ00:='02';--操作标志 0:正常结算 1:中途结算 2:年度结算 3:逃跑结算 01:跨年上年度 02:跨年本年度
LS_SFLB00:='B';--收费类别0:项目 1:YF_ZYCFMX 2:YF_YZYPSQ 3:药品 S:跨年上年度 B:跨年本年度
insert into ZY_JSBCMX(ZYID00,ID0000,MXID00,SFLB00,XMBH00,JZDH00,DJH000,XMDJ00,
XMSL00,HJJE00,CZRQ00,CZSJ00,CZY000,CZBZ00,FZDDH0,BZ0000)
select PZYID00,LS_ID0000_BND,0,LS_SFLB00,0,PJZDH00,A.DJH000,a.HJJE00,
1,a.HJJE00,LS_CZRQ00,LS_CZSJ00,PCZY000,LS_CZBZ00,substrb(LS_DDLSH0,1,30),'FYCF_BND'
from ZY_BRFY00 a where 1=1
and a.ZYID00=PZYID00
and a.BRID00=PBRID00
and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
and a.DJH000 not in (select bb.DJH000 from ZY_JSBCMX bb where bb.ZYID00=PZYID00 and bb.ID0000=LS_ID0000_SND and bb.CZBZ00='01') ;
begin
select sum(HJJE00) into LS_BNDFY0 from ZY_JSBCMX
where 1=1
and ZYID00=PZYID00
and ID0000=LS_ID0000_BND
and CZBZ00='02'
;
exception
when others then
LS_BNDFY0:=0;
end;
--如果上年度无费用直接返回N
if nvl(LS_SNDFY0,0)<=0 then
LS_JKFH00:='N';
end if;
--如果本年度无费用直接返回N
if nvl(LS_BNDFY0,0)<=0 then
LS_JKFH00:='N';
end if;
POUTSTR:=''||LS_ID0000_SND||''||''||LS_ID0000_BND||'';--[19][出参]备用出参xml格式输出
PZFHJJE:=nvl(LS_SNDFY0,0);--[15][出参]:自费合计金额或上年度合计金额
PYBHJJE:=nvl(LS_BNDFY0,0);--[16][出参]:医保合计金额或下年度合计金额
--PNDJSXX out varchar2 ,--[18][出参]年度结算返回信息=SF_ZY_SFQYYBKNDJS返回值
--第一位：是否启用Y:启用 N:不启用
--第二位: 上年度截止日期,费用
--第三位: 本年度截止日期,费用
PNDJSXX:=LS_JKFH00||'|';
PNDJSXX:=PNDJSXX||LS_RYRQ00||','||LS_SNDRQ0||','||nvl(LS_SNDFY0,0);
PNDJSXX:=PNDJSXX||'|';
PNDJSXX:=PNDJSXX||substrb(LS_DQRQ00,1,4)||'0101'||','||LS_BNDRQ0||','||nvl(LS_BNDFY0,0);
end if;
--1:医保跨年拆分--end
elsif PFYCFLX='Y' then
--1:出院结算 Y:婴儿拆分---begin---
--把数据插入到中间表-->婴儿费用
insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
select LS_CXLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','N'
from ZY_BRFY00 a where 1=1
and a.ZYID00=PZYID00
and a.BRID00=PBRID00
and a.YEXM00 is not null
and a.yexm00 not in ('全部')
and nvl(trim(a.YBFSDH),'0')='0'
and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
;
--冲销的单据号
insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
select LS_CXLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','N'
from ZY_BRFY00 a where 1=1
and a.ZYID00=PZYID00
and a.BRID00=PBRID00
and a.CXDJH0<>0
and a.CXDJH0 in (select bb.DJH000 from PAY_YJJ_MXCX bb where bb.BRID00=PBRID00 and bb.CXLSH0=LS_CXLSH0 and bb.DDLSH0=LS_DDLSH0 and bb.STATE='N' )
and a.DJH000 not in (select bb.DJH000 from PAY_YJJ_MXCX bb where bb.BRID00=PBRID00 and bb.CXLSH0=LS_CXLSH0 and bb.DDLSH0=LS_DDLSH0 and bb.STATE='N')
;
--查询数据婴儿自费金额
begin
select sum(AMOUNT) into PZFHJJE from PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=LS_CXLSH0 and DDLSH0=LS_DDLSH0 and STATE='N';
exception
when others then
PZFHJJE:=0;
end;
if nvl(PZFHJJE,0)<=0 then
PZFHJJE:=0;
PFKLSH0:='';
return;
end if;
--把数据插入到中间表-->非婴儿费用
insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
select LS_DDLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','Y'
from ZY_BRFY00 a where 1=1
and a.ZYID00=PZYID00 and a.BRID00=PBRID00
and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
and
(
( nvl(PZFHJJE,0)>0 and not exists (select 1 from PAY_YJJ_MXCX aa where aa.DJH000=a.DJH000 and aa.BRID00=PBRID00
and aa.CXLSH0=LS_CXLSH0 and aa.DDLSH0=LS_DDLSH0 and aa.STATE='N'))
or (nvl(PZFHJJE,0)<=0 and 1=1)
);
--查询数据医保金额
begin
select sum(AMOUNT) into PYBHJJE from PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=LS_CXLSH0 and DDLSH0=LS_DDLSH0 and STATE='Y';
exception
when others then
PYBHJJE:=0;
end;
--1:出院结算 Y:婴儿拆分---end---
--1:出院结算 M:医保项目拆分---begin---
elsif PFYCFLX='M' then
--把数据插入到中间表-->非医保项目--费用
insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
select LS_DDLSH0,LS_DDLSH0,D.DJH000,sum(A.HJJE00)HJJE00,D.BRID00,0,'0','N'
from ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D,BM_YGBM00 E
where 1=1
and nvl(trim(d.YBFSDH),'0')='0'
and (d.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or d.JZDH00+0=0)
and a.XMBH00 = C.SFXMID
and C.ZYFPID = B.FPXMID
and D.DJH000=a.DJH000
and a.KDYS00=e.YGBH00
and B.SFYLXM='Y'
and a.XMBH00<9999999900
and d.BRID00=PBRID00
and d.ZYID00=PZYID00
and a.JMBZ00<>'2'
and exists (
select 1 from BM_YBFPXM AA,VW_BM_YBSFDY CC,BM_YBSFXM DD
where AA.BH0000 = CC.YBZYFP
and AA.YBZXLB = CC.YBZXLB
and CC.YBBRLB = LS_YBBRLB
and CC.SFXMID = A.XMBH00
and CC.YBZXLB = DD.YBZXLB(+)
and CC.YBXMBH = DD.XMBH00(+)
and CC.SFYP00 = 'N'--是否药品,'0'费用,'1'零散处方,'2'医嘱处方
and AA.YBZXLB = LS_YBZXLB
and AA.SYBZ00 <> '1'
and nvl(CC.SFYBXM,'N')='N'
)
group by D.DJH000,D.BRID00;
--把数据插入到中间表-->非医保项目--药品
insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
select LS_DDLSH0,LS_DDLSH0,D.DJH000,sum(round(a.LSDJ00*a.YPZSL0*100)/100)HJJE00,D.BRID00,0,'1','N'
from YF_ZYCFMX A,YF_ZYCF00 c,ZY_BRFY00 d,BM_YD0000 e
where 1=1
and nvl(trim(D.YBFSDH),'0')='0'
and (d.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or d.JZDH00+0=0)
and a.CFLSH0=c.CFLSH0
and c.DJH000=d.DJH000
and a.YPNM00=e.YPNM00
and d.BRID00=PBRID00
and d.ZYID00=PZYID00
and a.SFZBY0='N'--是否自备药
and exists (
select 1 from BM_YBFPXM AA,VW_BM_YBSFDY CC,BM_YBSFXM DD
where AA.BH0000 = CC.YBZYFP
and AA.YBZXLB = CC.YBZXLB
and CC.YBBRLB = LS_YBBRLB
and CC.SFXMID = a.YPNM00
and CC.YBZXLB = DD.YBZXLB(+)
and CC.YBXMBH = DD.XMBH00(+)
and CC.SFYP00 = 'Y'--是否药品,'0'费用,'1'零散处方,'2'医嘱处方
and AA.YBZXLB = LS_YBZXLB
and AA.SYBZ00 <> '1'
and nvl(CC.SFYBXM,'N')='N'
)
group by D.DJH000,D.BRID00;
--把数据插入到中间表-->非医保项目--药品
insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
select LS_DDLSH0,LS_DDLSH0,b.DJH000,sum(round(a.LSDJ00*a.YPZSL0*100)/100)HJJE00,b.BRID00,0,'2','N'
from YF_YZYPSQ A,ZY_BRFY00 b,BM_YD0000 d,BM_YGBM00 E
where 1=1
and nvl(trim(b.YBFSDH),'0')='0'
and (b.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or b.JZDH00+0=0)
and a.DJH000=b.DJH000
and a.YPNM00=d.YPNM00
and a.YSGZH0=e.YGBH00
and b.BRID00=PBRID00
and b.ZYID00=PZYID00
and exists (
select 1 from BM_YBFPXM AA,VW_BM_YBSFDY CC,BM_YBSFXM DD
where AA.BH0000 = CC.YBZYFP
and AA.YBZXLB = CC.YBZXLB
and CC.YBBRLB = LS_YBBRLB
and CC.SFXMID = a.YPNM00
and CC.YBZXLB = DD.YBZXLB(+)
and CC.YBXMBH = DD.XMBH00(+)
and CC.SFYP00 = 'Y'--是否药品,'0'费用,'1'零散处方,'2'医嘱处方
and AA.YBZXLB = LS_YBZXLB
and AA.SYBZ00 <> '1'
and nvl(CC.SFYBXM,'N')='N'
)
group by b.DJH000,b.BRID00
;
--冲销的单据号
insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
select LS_CXLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','N'
from ZY_BRFY00 a where 1=1
and a.ZYID00=PZYID00
and a.BRID00=PBRID00
and a.CXDJH0<>0
and a.CXDJH0 in (select bb.DJH000 from PAY_YJJ_MXCX bb where bb.BRID00=PBRID00 and bb.CXLSH0=LS_CXLSH0 and bb.DDLSH0=LS_DDLSH0 and bb.STATE='N' )
and a.DJH000 not in (select bb.DJH000 from PAY_YJJ_MXCX bb where bb.BRID00=PBRID00 and bb.CXLSH0=LS_CXLSH0 and bb.DDLSH0=LS_DDLSH0 and bb.STATE='N')
;
--查询数据自费金额
begin
select sum(AMOUNT) into PZFHJJE from PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=LS_CXLSH0 and DDLSH0=LS_DDLSH0 and STATE='N';
exception
when others then
PZFHJJE:=0;
end;
if nvl(PZFHJJE,0)<=0 then
PZFHJJE:=0;
PFKLSH0:='';
return;
end if;
--把数据插入到中间表-->医保项目
insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,DJH000,AMOUNT,BRID00,JZDH00,TRADETYPE,STATE)
select LS_DDLSH0,LS_DDLSH0,a.DJH000,a.HJJE00,a.BRID00,0,'','Y'
from ZY_BRFY00 a where 1=1
and a.ZYID00=PZYID00 and a.BRID00=PBRID00
and (a.JZDH00 in (select k.JZDH00 from ZY_JZB000 k
where k.BRID00=PBRID00 and K.ZYID00=PZYID00 start with k.JZDH00=PJZDH00
connect by prior k.CXJZDH=k.JZDH00) or a.JZDH00+0=0)
and
(
( nvl(PZFHJJE,0)>0 and not exists (select 1 from PAY_YJJ_MXCX aa where aa.DJH000=a.DJH000 and aa.BRID00=PBRID00
and aa.CXLSH0=LS_CXLSH0 and aa.DDLSH0=LS_DDLSH0 and aa.STATE='N'))
or (nvl(PZFHJJE,0)<=0 and 1=1)
);
--查询数据医保金额
begin
select sum(AMOUNT) into PYBHJJE from PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=LS_CXLSH0 and DDLSH0=LS_DDLSH0 and STATE='Y';
exception
when others then
PYBHJJE:=0;
end;
end if;
--1:出院结算 M:医保项目拆分---end---
if PCOMMIT='Y' then
commit;
end if;
PZXZT00:='1';--执行状态 0:失败 1:成功
exception
when no_data_found then
PERRMSG:=substrb('执行过程SP_ZY_FYCFJS异常!错误原因:'||sqlerrm,1,200);
PZXZT00:='0';--执行状态 0:失败 1:成功
PERCODE:='no_data_found';
when others then
PERRMSG:=substrb('执行过程SP_ZY_FYCFJS异常!错误原因:'||sqlerrm,1,200);
PZXZT00:='0';--执行状态 0:失败 1:成功
PERCODE:='others';
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%